package com.example.mapper;

import com.example.pojo.Sample;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.jdbc.SQL;

import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

@Mapper
public interface SampleMapper {

    //查询digitalsample表中isCandidate为0的数据
    @Select("SELECT d.*, b.brandName AS brandName, df.defectName AS defectName " +
            "FROM digitalsample d " +
            "LEFT JOIN brand b ON d.brandID = b.brandID " +
            "LEFT JOIN defect df ON d.defectID = df.defectID " +
            "WHERE d.isCandidate = 0")
    List<Sample> find();

    //查询digitalsample表中isCandidate为1的数据
    @Select("SELECT d.*, b.brandName AS brandName, df.defectName AS defectName " +
            "FROM digitalsample d " +
            "LEFT JOIN brand b ON d.brandID = b.brandID " +
            "LEFT JOIN defect df ON d.defectID = df.defectID " +
            "WHERE d.isCandidate = 1")
    List<Sample> findb();

    //查询digitalsamplemachine表中所有数据并根据defectType从defect表中获取defectName,并且defectType不等于0
    @Select("SELECT d.*,  df.defectName AS defectName " +
            "FROM digitalsamplemachine d " +
            "LEFT JOIN defect df ON d.defectType = df.defectType " +
            "WHERE d.defectType != 0")
    List<Sample> findm();
    //查询digitalsamplemachine表中所有数据并根据defectType从defect表中获取defectName,并且defectType等于0
    @Select("SELECT d.*,  df.defectName AS defectName " +
            "FROM digitalsamplemachine d " +
            "LEFT JOIN defect df ON d.defectType = df.defectType " +
            "WHERE d.defectType = 0")
    List<Sample> findn();

    //根据id删除数字样
    @Select("delete from digitalsample where sampleID = #{sampleID}")
    void deleteById(Integer sampleID);

    //修改
    @Select("update digitalsample set defectID = #{defectID},defectGrade = #{defectGrade},update_Time = #{updateTime} where sampleID = #{sampleID}")
    void update(Sample sample);

    //修改digitalsample表中isCandidate的1修改为0,根据sampleID
    @Select("update digitalsample set isCandidate = 0,update_Time = #{updateTime} where sampleID = #{sampleID}")
    void upload(Sample sample);
    

//    //根据品牌名称、缺陷名称和缺陷等级筛选查询 isCandidate = 0
//    @SelectProvider(type = SampleMapperProvider.class, method = "findWithFilterSql")
//    List<Sample> findWithFilter(@Param("brandName") String brandName, @Param("defectName") String defectName, @Param("defectGrade") String defectGrade, @Param("isCandidate") int isCandidate);
//
//    //根据品牌名称、缺陷名称和缺陷等级筛选查询 isCandidate = 1
//    @SelectProvider(type = SampleMapperProvider.class, method = "findbWithFilterSql")
//    List<Sample> findbWithFilter(@Param("brandName") String brandName, @Param("defectName") String defectName, @Param("defectGrade") String defectGrade, @Param("isCandidate") int isCandidate);

    @Select("select defectType from defect where isMachine=#{i}&&defectName=#{brandName}")
    String findDefectType(String brandName, int i);

    //    @Select("select * from digitalsample where defectType=#{defectType}&&brandName=#{brandName}&&" +
//            "defectGrade=#{defectGrade}&&isCandidate=#{i};")
//    List<Sample> findDigitalSampleMsg(String defectType, String brandName, String defectGrade, int i);

//    class SampleMapperProvider {
//        public String findWithFilterSql(@Param("brandName") String brandName, @Param("defectName") String defectName, @Param("defectGrade") String defectGrade, @Param("isCandidate") int isCandidate) {
//            return new SQL() {{
//                SELECT("d.*, b.brandName AS brandName, df.defectName AS defectName");
//                FROM("digitalsample d");
//                LEFT_OUTER_JOIN("brand b ON d.brandID = b.brandID");
//                LEFT_OUTER_JOIN("defect df ON d.defectID = df.defectID");
//                WHERE("d.isCandidate = " + isCandidate );
//                if (brandName != null && !brandName.isEmpty()) {
//                    WHERE("b.brandName = #{brandName}");
//                }
//                if (defectName != null && !defectName.isEmpty()) {
//                    WHERE("df.defectName = #{defectName}");
//                }
//                if (defectGrade != null && !defectGrade.isEmpty()) {
//                    WHERE("d.defectGrade = #{defectGrade}");
//                }
//            }}.toString();
//        }
//
//        public String findbWithFilterSql(@Param("brandName") String brandName, @Param("defectName") String defectName, @Param("defectGrade") String defectGrade, @Param("isCandidate") int isCandidate) {
//            return new SQL() {{
//                SELECT("d.*, b.brandName AS brandName, df.defectName AS defectName");
//                FROM("digitalsample d");
//                LEFT_OUTER_JOIN("brand b ON d.brandID = b.brandID");
//                LEFT_OUTER_JOIN("defect df ON d.defectID = df.defectID");
//                WHERE("d.isCandidate = " + isCandidate);
//                if (brandName != null && !brandName.isEmpty()) {
//                    WHERE("b.brandName = #{brandName}");
//                }
//                if (defectName != null && !defectName.isEmpty()) {
//                    WHERE("df.defectName = #{defectName}");
//                }
//                if (defectGrade != null && !defectGrade.isEmpty()) {
//                    WHERE("d.defectGrade = #{defectGrade}");
//                }
//            }}.toString();
//        }
//    }

    //从defect表中根据isMachine=0的defectType对应的defectID，再将brandName，defectID,defectType,defectGrade,isCandidate,filename插入到digitalsample表中
    @Select("insert into digitalsample (defectID, filename,isCandidate,defectGrade) values (#{defectID},#{fileName},#{isCandidate},#{defectGrade})")
    void save(Sample sample);

    //向digitalsamplemachine表中插入数据
    @Select("insert into digitalsamplemachine (brandName,defectType,filename,machineID,postTime) values (#{brandName},#{defectType},#{fileName},#{machineID},#{postTime})")
    void savem(Sample sample);

    //从digitalsample表中获取所有isCandidate=0的filename
    @Select("select filename from digitalsample where isCandidate=0")
    List<String> getFilename();

    //根据filename从digitalsample表中获取brandID
    @Select("select brandID from digitalsample where filename=#{filename} LIMIT 1")
    Integer getBrandID(String filename);
    //根据filename从digitalsample表中获取defectID
    @Select("select defectID from digitalsample where filename=#{filename} LIMIT 1")
    Integer getDefectID(String filename);
    //根据brandID从brand表中获取brandName
    @Select("select brandName from brand where brandID=#{brandID}")
    String getBrandName(Integer brandID);
    //根据defectID从defect表中获取defectName
    @Select("select defectName from defect where defectID=#{defectID}")
    String getDefectName(Integer defectID);
    //根据defectName从defect表中获取defectID
    @Select("select defectID from defect where defectName=#{defectName}")
    Integer getDefectIDByName(String defectName);

    //从数字样库中获取所有不同的品牌名称、缺陷名称和缺陷等级
//    @Select("select distinct b.brandName, df.defectName, d.defectGrade from digitalsample d " +
//            "left join brand b on d.brandID = b.brandID " +
//            "left join defect df on d.defectID = df.defectID " +
//            "where d.isCandidate = 0")
//    List<Map<String, Object>> getAllNames();
    //从数字样库中获取所有不同的品牌名称、缺陷名称和缺陷等级
    @Select("SELECT DISTINCT b.brandName FROM digitalsample d " +
            "LEFT JOIN brand b ON d.brandID = b.brandID " +
            "WHERE d.isCandidate = 0")
    Set<String> getDistinctBrandNames();
    @Select("SELECT DISTINCT df.defectName FROM digitalsample d " +
            "LEFT JOIN defect df ON d.defectID = df.defectID " +
            "WHERE d.isCandidate = 0")
    Set<String> getDistinctDefectNames();
    @Select("SELECT DISTINCT defectGrade FROM digitalsample WHERE isCandidate = 0")
    Set<String> getDistinctDefectGrades();


    //从备选数字样库中获取所有不同的品牌名称、缺陷名称和缺陷等级
//    @Select("select distinct b.brandName, df.defectName, d.defectGrade from digitalsample d " +
//            "left join brand b on d.brandID = b.brandID " +
//            "left join defect df on d.defectID = df.defectID " +
//            "where d.isCandidate = 1")
//    List<Map<String, Object>> getAllNamesb();
    //从备选数字样库中获取所有不同的品牌名称、缺陷名称、缺陷等级和推荐指数
    @Select("SELECT DISTINCT b.brandName FROM digitalsample d " +
            "LEFT JOIN brand b ON d.brandID = b.brandID " +
            "WHERE d.isCandidate = 1")
    Set<String> getDistinctBrandNamesb();
    @Select("SELECT DISTINCT df.defectName FROM digitalsample d " +
            "LEFT JOIN defect df ON d.defectID = df.defectID " +
            "WHERE d.isCandidate = 1")
    Set<String> getDistinctDefectNamesb();
    @Select("SELECT DISTINCT defectGrade FROM digitalsample WHERE isCandidate = 1")
    Set<String> getDistinctDefectGradesb();
    @Select("SELECT DISTINCT NPS FROM digitalsample WHERE isCandidate = 1")
    Set<Double> getDistinctNPS();



    // SampleMapper.java 新增

    // 获取设备源图库所有品牌名称
    @Select("SELECT DISTINCT brandName FROM digitalsamplemachine WHERE defectType != 0")
    Set<String> getDistinctBrandNamesm();

    // 获取设备源图库所有设备编号
    @Select("SELECT DISTINCT machineID FROM digitalsamplemachine WHERE defectType != 0")
    Set<String> getDistinctMachineIDs();

    // 获取设备源图库所有缺陷类型名称
    @Select("SELECT DISTINCT df.defectName FROM digitalsamplemachine d " +
            "LEFT JOIN defect df ON d.defectType = df.defectType " +
            "WHERE d.defectType != 0")
    Set<String> getDistinctDefectTypesm();

    // 获取正常图片所有品牌名称
    @Select("SELECT DISTINCT brandName FROM digitalsamplemachine WHERE defectType = 0")
    Set<String> getDistinctBrandNamesn();
    //获取正常图片所有设备id
    @Select("SELECT DISTINCT machineID FROM digitalsamplemachine WHERE defectType = 0")
    Set<String> getDistinctMachineIDsn();


    // 新增缺陷类型查询
    @Select("SELECT DISTINCT defectType FROM defect WHERE defectName = #{defectName}")
    List<String> findDefectTypesByName(String defectName);
}
